﻿using System.Data;
using System;

namespace iChip.Hotspots.DB
{
    /// <summary>
    /// 
    /// </summary>
    public class StoredProcedures : StoredProcedures_Base
    {

        private MainDB _db;

        public StoredProcedures(MainDB db)
            : base(db)
        {
            _db = db;
        }



        #region Hotspot

        public DataSet Get_Field_by_Hotspot_Mac(string Hotspot_Mac, int User_Type)
        {
            IDbCommand cmd = _db.CreateCommand("Get_Field_by_Hotspot_Mac", true);
            _db.AddParameter(cmd, "Hotspot_Mac", DbType.String, Hotspot_Mac);
            _db.AddParameter(cmd, "User_Type", DbType.Int32, User_Type);
            DataSet ds = CreateDataSet(cmd);

            return ds;
        }

        public DataSet Select_FE_Hotspot_Mac(string Hotspot_Mac, string User_Mac)
        {
            IDbCommand cmd = _db.CreateCommand("Select_FE_Hotspot_Mac", true);
            _db.AddParameter(cmd, "Hotspot_Mac", DbType.String, Hotspot_Mac);
            _db.AddParameter(cmd, "User_Mac", DbType.String, User_Mac);
            DataSet ds = CreateDataSet(cmd);

            return ds;
        }

        public DataTable Select_Hotspots_Paging(int OffsetRows, int FetchRows)
        {
            IDbCommand cmd = _db.CreateCommand("Select_Hotspots_Paging", true);
            _db.AddParameter(cmd, "OffsetRows", DbType.Int32, OffsetRows);
            _db.AddParameter(cmd, "FetchRows", DbType.Int32, FetchRows);
            return CreateDataTable(cmd);
        }

        public DataTable Insert_Hotspots(string Hotspot_Mac, string Hotspot_Ip, string Hotspot_Nasid, bool Hotspot_Status,
                            string Hotspot_City, string Hotspot_Region, string Hotspot_Address, string Hotspot_Location, string Hotspot_Name, string Hotspot_Type, int Account_Id)
        {
            IDbCommand cmd = _db.CreateCommand("Insert_Hotspots", true);
            _db.AddParameter(cmd, "Hotspot_Mac", DbType.String, Hotspot_Mac);
            _db.AddParameter(cmd, "Hotspot_Ip", DbType.String, Hotspot_Ip);
            _db.AddParameter(cmd, "Hotspot_Nasid", DbType.String, Hotspot_Nasid);
            _db.AddParameter(cmd, "Hotspot_Status", DbType.Boolean, Hotspot_Status);
            _db.AddParameter(cmd, "Hotspot_City", DbType.String, Hotspot_City);
            _db.AddParameter(cmd, "Hotspot_Region", DbType.String, Hotspot_Region);
            _db.AddParameter(cmd, "Hotspot_Address", DbType.String, Hotspot_Address);
            _db.AddParameter(cmd, "Hotspot_Location", DbType.String, Hotspot_Location);
            _db.AddParameter(cmd, "Hotspot_Name", DbType.String, Hotspot_Name);
            _db.AddParameter(cmd, "Hotspot_Type", DbType.String, Hotspot_Type);
            _db.AddParameter(cmd, "Account_Id", DbType.Int32, Account_Id);
            return CreateDataTable(cmd);
        }

        public void Update_Hotspots(int Hotspot_Id, string Hotspot_Mac, string Hotspot_Ip, string Hotspot_Nasid, bool Hotspot_Status,
                                    string Hotspot_City, string Hotspot_Region, string Hotspot_Address, string Hotspot_Location, string Hotspot_Name, string Hotspot_Type)
        {
            IDbCommand cmd = _db.CreateCommand("Update_Hotspots", true);
            _db.AddParameter(cmd, "Hotspot_Id", DbType.Int32, Hotspot_Id);
            _db.AddParameter(cmd, "Hotspot_Mac", DbType.String, Hotspot_Mac);
            _db.AddParameter(cmd, "Hotspot_Ip", DbType.String, Hotspot_Ip);
            _db.AddParameter(cmd, "Hotspot_Nasid", DbType.String, Hotspot_Nasid);
            _db.AddParameter(cmd, "Hotspot_Status", DbType.Boolean, Hotspot_Status);
            _db.AddParameter(cmd, "Hotspot_City", DbType.String, Hotspot_City);
            _db.AddParameter(cmd, "Hotspot_Region", DbType.String, Hotspot_Region);
            _db.AddParameter(cmd, "Hotspot_Address", DbType.String, Hotspot_Address);
            _db.AddParameter(cmd, "Hotspot_Location", DbType.String, Hotspot_Location);
            _db.AddParameter(cmd, "Hotspot_Name", DbType.String, Hotspot_Name);
            _db.AddParameter(cmd, "Hotspot_Type", DbType.String, Hotspot_Type);
            CreateDataTable(cmd);
        }

        /// <summary>
        /// Update Trạng thái LiveTime của Hotspots
        /// </summary>
        /// <param name="Hotspot_Mac"></param>
        public void Update_Hotspots_LiveTime(string Hotspot_Mac, string Hotspot_Ip, string Hotspot_Nasid)
        {
            IDbCommand cmd = _db.CreateCommand("Update_Hotspots_LiveTime", true);
            _db.AddParameter(cmd, "Hotspot_Mac", DbType.String, Hotspot_Mac);
            _db.AddParameter(cmd, "Hotspot_Ip", DbType.String, Hotspot_Ip);
            _db.AddParameter(cmd, "Hotspot_Nasid", DbType.String, Hotspot_Nasid);
            CreateDataTable(cmd);
        }

        /// <summary>
        /// Get từ Hotspot những hotspot có status =1 và livetime > 10 phút chưa được cập nhật
        /// </summary>
        /// <returns></returns>
        public DataTable Select_Hotspots_Down()
        {
            IDbCommand cmd = _db.CreateCommand("Select_Hotspots_Down", true);
            return CreateDataTable(cmd);
        }

        /// <summary>
        ///  Lấy tất cả Hotspot để hiển thị trên bản đồ
        /// </summary>
        /// <returns></returns>
        public DataTable Select_Hotspots()
        {
            IDbCommand cmd = _db.CreateCommand("Select_Hotspots", true);
            return CreateDataTable(cmd);
        }

        public DataTable Select_Hotspots_by_Id(int Hotspot_Id)
        {
            IDbCommand cmd = _db.CreateCommand("Select_Hotspots_by_Id", true);
            _db.AddParameter(cmd, "Hotspot_Id", DbType.Int32, Hotspot_Id);
            return CreateDataTable(cmd);
        }


        public void Delete_Hotspots(int Hotspot_Id)
        {
            IDbCommand cmd = _db.CreateCommand("Delete_Hotspots", true);
            _db.AddParameter(cmd, "Hotspot_Id", DbType.Int32, Hotspot_Id);
            CreateDataTable(cmd);
        }

        #endregion

        #region Values & Field
        public void Insert_Values(string User_Mac, int Field_Id, string Value, int ValueParent_Id)
        {
            IDbCommand cmd = _db.CreateCommand("Insert_Values", true);
            _db.AddParameter(cmd, "User_Mac", DbType.String, User_Mac);
            _db.AddParameter(cmd, "Field_Id", DbType.Int32, Field_Id);
            _db.AddParameter(cmd, "Value", DbType.String, Value);
            _db.AddParameter(cmd, "ValueParent_Id", DbType.Int32, ValueParent_Id);
            CreateDataTable(cmd);
        }

        public void Insert_Fields(int Field_Id, int Method_Id, string Field_Name, int FieldType_Id, bool Field_IsRequire)
        {
            IDbCommand cmd = _db.CreateCommand("Insert_Fields", true);
            _db.AddParameter(cmd, "Field_Id", DbType.Int32, Field_Id);
            _db.AddParameter(cmd, "Method_Id", DbType.Int32, Method_Id);
            _db.AddParameter(cmd, "Field_Name", DbType.String, Field_Name);
            _db.AddParameter(cmd, "FieldType_Id", DbType.Int32, FieldType_Id);
            _db.AddParameter(cmd, "Field_IsRequire", DbType.Boolean, Field_IsRequire);
            CreateDataTable(cmd);
        }

        public void Insert_FieldValues(int FieldValue_Id, int Field_Id, string Field_Name, string FieldValue_Value)
        {
            IDbCommand cmd = _db.CreateCommand("Insert_FieldValues", true);
            _db.AddParameter(cmd, "FieldValue_Id", DbType.Int32, FieldValue_Id);
            _db.AddParameter(cmd, "Field_Id", DbType.Int32, Field_Id);
            _db.AddParameter(cmd, "Field_Name", DbType.String, Field_Name);
            _db.AddParameter(cmd, "FieldValue_Value", DbType.String, FieldValue_Value);
            CreateDataTable(cmd);
        }


        public void Delete_Field_by_Id(int Field_Id)
        {
            IDbCommand cmd = _db.CreateCommand("Delete_Field_by_Id", true);
            _db.AddParameter(cmd, "Field_Id", DbType.Int32, Field_Id);
            CreateDataTable(cmd);
        }

        public DataTable Select_FieldValue_by_FieldId(int Field_Id)
        {
            IDbCommand cmd = _db.CreateCommand("Select_FieldValue_by_FieldId", true);
            _db.AddParameter(cmd, "Field_Id", DbType.Int32, Field_Id);
            return CreateDataTable(cmd);
        }

        public void Delete_Field_by_Method_Ids(int Method_Id)
        {
            IDbCommand cmd = _db.CreateCommand("Delete_Field_by_Method_Ids", true);
            _db.AddParameter(cmd, "Method_Id", DbType.Int32, Method_Id);
            CreateDataTable(cmd);
        }



        #endregion

        #region Splash
        public DataTable Select_Splash_Paging(int OffsetRows, int FetchRows, int Hotspot_Id)
        {
            IDbCommand cmd = _db.CreateCommand("Select_Splash_Paging", true);
            _db.AddParameter(cmd, "OffsetRows", DbType.Int32, OffsetRows);
            _db.AddParameter(cmd, "FetchRows", DbType.Int32, FetchRows);
            _db.AddParameter(cmd, "Hotspot_Id", DbType.Int32, Hotspot_Id);
            return CreateDataTable(cmd);
        }


        public DataTable Insert_Splashs(string Hotspot_Mac, int Hotspot_Id, string Splash_Name, DateTime Splash_StartTime, DateTime Splash_EndTime, int User_Type, string Splash_Content, string Splash_UrlRedirect, bool Splash_IsDefault)
        {
            IDbCommand cmd = _db.CreateCommand("Insert_Splashs", true);
            _db.AddParameter(cmd, "Hotspot_Mac", DbType.String, Hotspot_Mac);
            _db.AddParameter(cmd, "Hotspot_Id", DbType.Int32, Hotspot_Id);
            _db.AddParameter(cmd, "Splash_Name", DbType.String, Splash_Name);
            _db.AddParameter(cmd, "Splash_StartTime", DbType.DateTime, Splash_StartTime);
            _db.AddParameter(cmd, "Splash_EndTime", DbType.DateTime, Splash_EndTime);
            _db.AddParameter(cmd, "User_Type", DbType.Int32, User_Type);
            _db.AddParameter(cmd, "Splash_Content", DbType.String, Splash_Content);
            _db.AddParameter(cmd, "Splash_UrlRedirect", DbType.String, Splash_UrlRedirect);
            _db.AddParameter(cmd, "Splash_IsDefault", DbType.Boolean, Splash_IsDefault);
            return CreateDataTable(cmd);
        }

        public void Update_Splashs(int Splash_Id, string Hotspot_Mac, int Hotspot_Id, string Splash_Name, DateTime Splash_StartTime, DateTime Splash_EndTime, int User_Type, string Splash_Content, string Splash_UrlRedirect)
        {
            IDbCommand cmd = _db.CreateCommand("Update_Splashs", true);
            _db.AddParameter(cmd, "Splash_Id", DbType.Int32, Splash_Id);
            _db.AddParameter(cmd, "Hotspot_Mac", DbType.String, Hotspot_Mac);
            _db.AddParameter(cmd, "Hotspot_Id", DbType.Int32, Hotspot_Id);
            _db.AddParameter(cmd, "Splash_Name", DbType.String, Splash_Name);
            _db.AddParameter(cmd, "Splash_StartTime", DbType.DateTime, Splash_StartTime);
            _db.AddParameter(cmd, "Splash_EndTime", DbType.DateTime, Splash_EndTime);
            _db.AddParameter(cmd, "User_Type", DbType.Int32, User_Type);
            _db.AddParameter(cmd, "Splash_Content", DbType.String, Splash_Content);
            _db.AddParameter(cmd, "Splash_UrlRedirect", DbType.String, Splash_UrlRedirect);
            CreateDataTable(cmd);
        }

        public DataTable Select_Splash_by_Id(int Splash_Id)
        {
            IDbCommand cmd = _db.CreateCommand("Select_Splash_by_Id", true);
            _db.AddParameter(cmd, "Splash_Id", DbType.Int32, Splash_Id);
            return CreateDataTable(cmd);
        }


        public void Delete_Splashs(int Splash_Id)
        {
            IDbCommand cmd = _db.CreateCommand("Delete_Splashs", true);
            _db.AddParameter(cmd, "Splash_Id", DbType.Int32, Splash_Id);
            CreateDataTable(cmd);
        }

        #endregion

        #region Logs

        public void Insert_Logs(string Hotspot_Mac, string User_Mac, string Log_OS, string Log_Device, string Method_Type, string Log_Brower)
        {
            IDbCommand cmd = _db.CreateCommand("Insert_Logs", true);
            _db.AddParameter(cmd, "Hotspot_Mac", DbType.String, Hotspot_Mac);
            _db.AddParameter(cmd, "User_Mac", DbType.String, User_Mac);
            _db.AddParameter(cmd, "Log_OS", DbType.String, Log_OS);
            _db.AddParameter(cmd, "Log_Device", DbType.String, Log_Device);
            _db.AddParameter(cmd, "Method_Type", DbType.String, Method_Type);
            _db.AddParameter(cmd, "Log_Brower", DbType.String, Log_Brower);
            CreateDataTable(cmd);
        }

        /// <summary>
        /// Lấy Dữ liệu từ bảng Log được phân trangs
        /// </summary>
        /// <param name="OffsetRows"></param>
        /// <param name="FetchRows"></param>
        /// <returns></returns>
        public DataTable Select_Logs_Paging(int OffsetRows, int FetchRows)
        {
            IDbCommand cmd = _db.CreateCommand("Select_Logs_Paging", true);
            _db.AddParameter(cmd, "OffsetRows", DbType.Int32, OffsetRows);
            _db.AddParameter(cmd, "FetchRows", DbType.Int32, FetchRows);
            return CreateDataTable(cmd);
        }

        /// <summary>
        /// Thống kế số lượng truy cập trong tháng theo ngày
        /// </summary>
        /// <param name="month"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public DataTable Select_Logs_GroupBy_Day(int Month, int Year)
        {
            IDbCommand cmd = _db.CreateCommand("Select_Logs_GroupBy_Day", true);
            _db.AddParameter(cmd, "Month", DbType.Int32, Month);
            _db.AddParameter(cmd, "Year", DbType.Int32, Year);
            return CreateDataTable(cmd);
        }

        /// <summary>
        /// Thống kê số lượng truy cập trong tháng theo ngày
        /// </summary>
        /// <param name="Month"></param>
        /// <returns></returns>

        #endregion

        #region Method

        public DataTable Select_Method_by_SplashId(int Splash_Id)
        {
            IDbCommand cmd = _db.CreateCommand("Select_Method_by_SplashId", true);
            _db.AddParameter(cmd, "Splash_Id", DbType.Int32, Splash_Id);
            return CreateDataTable(cmd);
        }

        /// <summary>
        /// Lay thong danh sach cac method va cac field theo tung method theo moi Splash
        /// </summary>
        /// <param name="Splash_Id"></param>
        /// <returns></returns>
        public DataSet Select_Method_Field_by_SplashId(int Splash_Id)
        {
            IDbCommand cmd = _db.CreateCommand("Select_Method_Field_by_SplashId", true);
            _db.AddParameter(cmd, "Splash_Id", DbType.Int32, Splash_Id);
            DataSet ds = CreateDataSet(cmd);

            return ds;
        }

        public void Insert_Methods(int Splash_Id, string Method_Type, int Method_Order, bool Method_Status)
        {
            IDbCommand cmd = _db.CreateCommand("Insert_Methods", true);
            _db.AddParameter(cmd, "Splash_Id", DbType.Int32, Splash_Id);
            _db.AddParameter(cmd, "Method_Type", DbType.String, Method_Type);
            _db.AddParameter(cmd, "Method_Order", DbType.Int32, Method_Order);
            _db.AddParameter(cmd, "Method_Status", DbType.Boolean, Method_Status);

            CreateDataTable(cmd);
        }

        public void Update_Methods(int Method_Id, int Splash_Id, string Method_Type, int Method_Order, bool Method_Status)
        {
            IDbCommand cmd = _db.CreateCommand("Update_Methods", true);
            _db.AddParameter(cmd, "Method_Id", DbType.Int32, Method_Id);
            _db.AddParameter(cmd, "Splash_Id", DbType.Int32, Splash_Id);
            _db.AddParameter(cmd, "Method_Type", DbType.String, Method_Type);
            _db.AddParameter(cmd, "Method_Order", DbType.Int32, Method_Order);
            _db.AddParameter(cmd, "Method_Status", DbType.Boolean, Method_Status);

            CreateDataTable(cmd);
        }

        #endregion

        #region Bảng User

        public void Insert_Users(string User_Mac, string User_Name, string User_Email, string User_Phone, string User_Address, string User_Link, bool User_Status, DateTime User_Birthday, int User_Type)
        {
            IDbCommand cmd = _db.CreateCommand("Update_Users", true);
            _db.AddParameter(cmd, "User_Mac", DbType.String, User_Mac);
            _db.AddParameter(cmd, "User_Name", DbType.String, User_Name);
            _db.AddParameter(cmd, "User_Email", DbType.String, User_Email);
            _db.AddParameter(cmd, "User_Phone", DbType.String, User_Phone);
            _db.AddParameter(cmd, "User_Address", DbType.String, User_Address);
            _db.AddParameter(cmd, "User_Link", DbType.String, User_Link);
            _db.AddParameter(cmd, "User_Status", DbType.Boolean, User_Status);
            _db.AddParameter(cmd, "User_Birthday", DbType.DateTime, User_Birthday);
            _db.AddParameter(cmd, "User_Type", DbType.Int32, User_Type);
            CreateDataTable(cmd);
        }

        public void Update_Users(string User_Mac, string User_Name, string User_Email, string User_Phone, string User_Address, string User_Link, bool User_Status, DateTime User_Birthday, int User_Type)
        {
            IDbCommand cmd = _db.CreateCommand("Update_Users", true);
            _db.AddParameter(cmd, "User_Mac", DbType.String, User_Mac);
            _db.AddParameter(cmd, "User_Name", DbType.String, User_Name);
            _db.AddParameter(cmd, "User_Email", DbType.String, User_Email);
            _db.AddParameter(cmd, "User_Phone", DbType.String, User_Phone);
            _db.AddParameter(cmd, "User_Address", DbType.String, User_Address);
            _db.AddParameter(cmd, "User_Link", DbType.String, User_Link);
            _db.AddParameter(cmd, "User_Status", DbType.Boolean, User_Status);
            _db.AddParameter(cmd, "User_Birthday", DbType.DateTime, User_Birthday);
            _db.AddParameter(cmd, "User_Type", DbType.Int32, User_Type);
            CreateDataTable(cmd);
        }

        #endregion

        #region Brander
        public DataTable Insert_Branders(int Brander_Id, string Brander_Name, string Brander_User, string Brander_Password, string Brander_Email, string Brander_Phone,
                                        string Brander_Avatar, string Brander_Fanpage, string Brander_Facebook_AppId, string Brander_Facebook_AppSecret, string Brander_FaceBook_ShareName,
                                        string Brander_Facebook_ShareLink, string Brander_FaceBook_SharePicture, string Brander_FaceBook_ShareCaption, string Brander_FaceBook_ShareDescription,
                                        string Brander_Video_Link, string Brander_App_Link, string Brander_Access_Code, DateTime Brander_StartTime, DateTime Brander_EndTime, bool Brander_Status,
                                        string Brander_SplashDefault, string Brander_UrlRedirect)
        {
            IDbCommand cmd = _db.CreateCommand("Insert_Branders", true);
            _db.AddParameter(cmd, "Brander_Id", DbType.Int32, Brander_Id);
            _db.AddParameter(cmd, "Brander_Name", DbType.String, Brander_Name);
            _db.AddParameter(cmd, "Brander_User", DbType.String, Brander_User);
            _db.AddParameter(cmd, "Brander_Password", DbType.String, Brander_Password);
            _db.AddParameter(cmd, "Brander_Email", DbType.String, Brander_Email);
            _db.AddParameter(cmd, "Brander_Phone", DbType.String, Brander_Phone);
            _db.AddParameter(cmd, "Brander_Avatar", DbType.String, Brander_Avatar);
            _db.AddParameter(cmd, "Brander_Fanpage", DbType.String, Brander_Fanpage);

            _db.AddParameter(cmd, "Brander_Facebook_AppId", DbType.String, Brander_Facebook_AppId);
            _db.AddParameter(cmd, "Brander_Facebook_AppSecret", DbType.String, Brander_Facebook_AppSecret);
            _db.AddParameter(cmd, "Brander_FaceBook_ShareName", DbType.String, Brander_FaceBook_ShareName);
            _db.AddParameter(cmd, "Brander_Facebook_ShareLink", DbType.String, Brander_Facebook_ShareLink);
            _db.AddParameter(cmd, "Brander_FaceBook_SharePicture", DbType.String, Brander_FaceBook_SharePicture);
            _db.AddParameter(cmd, "Brander_FaceBook_ShareCaption", DbType.String, Brander_FaceBook_ShareCaption);
            _db.AddParameter(cmd, "Brander_FaceBook_ShareDescription", DbType.String, Brander_FaceBook_ShareDescription);

            _db.AddParameter(cmd, "Brander_Video_Link", DbType.String, Brander_Video_Link);
            _db.AddParameter(cmd, "Brander_App_Link", DbType.String, Brander_App_Link);
            _db.AddParameter(cmd, "Brander_Access_Code", DbType.String, Brander_Access_Code);
            _db.AddParameter(cmd, "Brander_StartTime", DbType.DateTime, Brander_StartTime);
            _db.AddParameter(cmd, "Brander_EndTime", DbType.DateTime, Brander_EndTime);
            _db.AddParameter(cmd, "Brander_Status", DbType.Boolean, Brander_Status);
            _db.AddParameter(cmd, "Brander_SplashDefault", DbType.String, Brander_SplashDefault);
            _db.AddParameter(cmd, "Brander_UrlRedirect", DbType.String, Brander_UrlRedirect);

            return CreateDataTable(cmd);
        }

        public DataTable Select_Brander_Paging(int OffsetRows, int FetchRows)
        {
            IDbCommand cmd = _db.CreateCommand("Select_Brander_Paging", true);
            _db.AddParameter(cmd, "OffsetRows", DbType.Int32, OffsetRows);
            _db.AddParameter(cmd, "FetchRows", DbType.Int32, FetchRows);
            return CreateDataTable(cmd);
        }

        #endregion

        #region Account
        public void Insert_Accounts(string Account_Name,string Account_Password,string Account_FulNname,bool Account_Status,string Account_Email,string Account_Phone,string Account_Avatar,bool Account_SuperAdmin,int Brander_Id, int Account_Role)
        {
            IDbCommand cmd = _db.CreateCommand("Insert_Accounts", true);
            _db.AddParameter(cmd, "Account_Name", DbType.String, Account_Name);
            _db.AddParameter(cmd, "Account_Password", DbType.String, Account_Password);
            _db.AddParameter(cmd, "Account_FulNname", DbType.String, Account_FulNname);
            _db.AddParameter(cmd, "Account_Status", DbType.Boolean, Account_Status);
            _db.AddParameter(cmd, "Account_Email", DbType.String, Account_Email);
            _db.AddParameter(cmd, "Account_Phone", DbType.String, Account_Phone);
            _db.AddParameter(cmd, "Account_Avatar", DbType.String, Account_Avatar);
            _db.AddParameter(cmd, "Account_SuperAdmin", DbType.Boolean, Account_SuperAdmin);
            _db.AddParameter(cmd, "Brander_Id", DbType.Int32, Brander_Id);
            _db.AddParameter(cmd, "Account_Role", DbType.Int32, Account_Role);
            CreateDataTable(cmd);
        }
        #endregion

    }
} // End of namespace



